package com.lqf.compare.mysql;

import com.lqf.compare.mysql.model.ColumnInfo;
import com.lqf.compare.mysql.model.IndexInfo;
import com.lqf.compare.mysql.model.TableInfo;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.concurrent.*;
import javax.sql.DataSource;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.*;

@Service
public class DatabaseCompareService {

    // 使用自定义线程池，根据CPU核心数设置合适的线程数
    private final ExecutorService executorService = Executors.newFixedThreadPool(
            Math.min(20, Math.max(4, Runtime.getRuntime().availableProcessors() * 2))
    );

    // 修改 createDataSource 方法，使用连接池配置
    private DataSource createDataSource(String url, String username, String password) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);

        // 添加连接池配置参数以提高性能
        // 注意：实际项目中建议使用专业的连接池如HikariCP或Druid
        System.setProperty("com.mysql.cj.jdbc.Driver", "");

        return dataSource;
    }

    // 修改 compareTableStructure 方法参数名，明确主库和目标库
    public Map<String, Object> compareTableStructure(String masterDbUrl, String targetDbUrl,
                                                     String masterUsername, String masterPassword,
                                                     String targetUsername, String targetPassword,
                                                     String tableName) {
        Map<String, Object> result = new HashMap<>();
        DataSource masterDs = createDataSource(masterDbUrl, masterUsername, masterPassword);
        DataSource targetDs = createDataSource(targetDbUrl, targetUsername, targetPassword);
        JdbcTemplate masterJdbcTemplate = new JdbcTemplate(masterDs);
        JdbcTemplate targetJdbcTemplate = new JdbcTemplate(targetDs);

        try {
            TableInfo masterInfo = getTableInfo(masterJdbcTemplate, tableName);
            TableInfo targetInfo = getTableInfo(targetJdbcTemplate, tableName);

            if (masterInfo == null) {
                result.put("error", "表在主库中不存在: " + tableName);
                return result;
            }
            if (targetInfo == null) {
                result.put("error", "表在目标库中不存在: " + tableName);
                return result;
            }

            // 比较逻辑
            List<String> differences = new ArrayList<>();
            DatabaseCompareUtil.compareTableComments(masterInfo, targetInfo, differences);
            DatabaseCompareUtil.compareColumns(masterInfo, targetInfo, differences);
            DatabaseCompareUtil.compareIndexes(masterInfo, targetInfo, differences);

            result.put("table1", masterInfo); // 主库
            result.put("table2", targetInfo); // 目标库
            result.put("differences", differences);
            result.put("hasDifference", !differences.isEmpty());

            // 标记差异的列（只标记目标库与主库不一致的地方）
            DatabaseCompareUtil.markColumnDifferences(masterInfo, targetInfo);
            DatabaseCompareUtil.markIndexDifferences(masterInfo, targetInfo);

            // 生成SQL修改语句（将目标库修改为主库结构）
            if (!differences.isEmpty()) {
                List<String> alterSqlStatements = DatabaseCompareUtil.generateAlterSqlStatements(masterInfo, targetInfo, tableName, targetDbUrl);
                result.put("alterSqlStatements", alterSqlStatements);
            }

        } catch (Exception e) {
            result.put("error", "比较过程中发生错误: " + e.getMessage());
        } finally {
            // 实际应用中可能需要更精细的资源管理
        }
        return result;
    }

    // 修改 compareAllTables 方法，通过批量查询优化性能
    public Map<String, Object> compareAllTables(String masterDbUrl, String targetDbUrl,
                                                String masterUsername, String masterPassword,
                                                String targetUsername, String targetPassword) {
        long startTime = System.currentTimeMillis();
        Map<String, Object> result = new HashMap<>();

        DataSource masterDs = createDataSource(masterDbUrl, masterUsername, masterPassword);
        DataSource targetDs = createDataSource(targetDbUrl, targetUsername, targetPassword);

        JdbcTemplate masterJdbcTemplate = new JdbcTemplate(masterDs);
        JdbcTemplate targetJdbcTemplate = new JdbcTemplate(targetDs);

        try {
            // 并行获取两个数据库的所有表结构信息
            CompletableFuture<Map<String, TableInfo>> masterTableInfosFuture =
                    CompletableFuture.supplyAsync(() -> getAllTableInfos(masterJdbcTemplate), executorService);

            CompletableFuture<Map<String, TableInfo>> targetTableInfosFuture =
                    CompletableFuture.supplyAsync(() -> getAllTableInfos(targetJdbcTemplate), executorService);

            // 等待两个任务完成
            Map<String, TableInfo> masterTableInfos = masterTableInfosFuture.get(60, TimeUnit.SECONDS);
            Map<String, TableInfo> targetTableInfos = targetTableInfosFuture.get(60, TimeUnit.SECONDS);

            System.out.println( (System.currentTimeMillis() - startTime));

            List<Map<String, Object>> tableDifferences = new ArrayList<>();

            // 处理主库中的所有表
            for (Map.Entry<String, TableInfo> entry : masterTableInfos.entrySet()) {
                String tableName = entry.getKey();
                TableInfo masterInfo = entry.getValue();

                if (targetTableInfos.containsKey(tableName)) {
                    // 两个库都有这个表，进行对比
                    TableInfo targetInfo = targetTableInfos.get(tableName);
                    Map<String, Object> tableResult = compareTwoTableInfos(masterInfo, targetInfo);

                    if (tableResult.containsKey("hasDifference") && (Boolean) tableResult.get("hasDifference")) {
                        Map<String, Object> diffItem = new HashMap<>();
                        diffItem.put("tableName", tableName);
                        diffItem.put("hasDifference", true);
                        diffItem.putAll(tableResult);
                        tableDifferences.add(diffItem);
                    }
                } else {
                    // 目标库缺少表
                    Map<String, Object> missingTableResult = new HashMap<>();
                    missingTableResult.put("tableName", tableName);
                    missingTableResult.put("hasDifference", true);
                    missingTableResult.put("differenceType", "missing_in_target");
                    missingTableResult.put("message", "表在目标库中缺失");

                    // 生成创建表的SQL语句
                    List<String> createSqlStatements = DatabaseCompareUtil.generateCreateTableSqlStatements(masterInfo, tableName);
                    missingTableResult.put("alterSqlStatements", createSqlStatements);
                    missingTableResult.put("table1", masterInfo);

                    tableDifferences.add(missingTableResult);
                }
            }

            result.put("tableDifferences", tableDifferences);
            result.put("hasDifference", !tableDifferences.isEmpty());
            result.put("totalTables", masterTableInfos.size());
            result.put("differentTables", tableDifferences.size());

        } catch (Exception e) {
            result.put("error", "比较过程中发生错误: " + e.getMessage());
        } finally {
            // 关闭数据源
            if (masterDs instanceof HikariDataSource) {
                ((HikariDataSource) masterDs).close();
            }
            if (targetDs instanceof HikariDataSource) {
                ((HikariDataSource) targetDs).close();
            }
        }

        long endTime = System.currentTimeMillis();
        result.put("executionTime", (endTime - startTime) + " ms");

        return result;
    }

    // 新增方法：一次性获取所有表结构信息
    private Map<String, TableInfo> getAllTableInfos(JdbcTemplate jdbcTemplate) {
        Map<String, TableInfo> tableInfos = new HashMap<>();

        // 一次性查询所有表的所有列信息，包含完整的数据类型信息（包括长度）
        String columnSql = "SELECT " +
                "c.TABLE_NAME, " +
                "c.COLUMN_NAME, " +
                "CASE " +
                "  WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CONCAT(c.DATA_TYPE, '(', c.CHARACTER_MAXIMUM_LENGTH, ')') " +
                "  WHEN c.DATETIME_PRECISION IS NOT NULL AND c.DATA_TYPE = 'datetime' THEN CONCAT(c.DATA_TYPE, '(', c.DATETIME_PRECISION, ')') " +
                "  WHEN c.DATETIME_PRECISION IS NOT NULL AND c.DATA_TYPE = 'time' THEN CONCAT(c.DATA_TYPE, '(', c.DATETIME_PRECISION, ')') " +
                "  WHEN c.NUMERIC_PRECISION IS NOT NULL AND c.DATA_TYPE IN ('decimal', 'numeric') AND c.NUMERIC_SCALE IS NOT NULL THEN CONCAT(c.DATA_TYPE, '(', c.NUMERIC_PRECISION, ',', c.NUMERIC_SCALE, ')') " +
                "  WHEN c.NUMERIC_PRECISION IS NOT NULL AND c.DATA_TYPE IN ('decimal', 'numeric') THEN CONCAT(c.DATA_TYPE, '(', c.NUMERIC_PRECISION, ')') " +
                "  WHEN c.NUMERIC_PRECISION IS NOT NULL AND c.DATA_TYPE IN ('tinyint', 'smallint', 'mediumint', 'int', 'bigint') THEN CONCAT(c.DATA_TYPE, '(', c.NUMERIC_PRECISION, ')') " +
                "  ELSE c.DATA_TYPE " +
                "END AS FULL_DATA_TYPE, " +
                "c.COLUMN_COMMENT, " +
                "c.IS_NULLABLE, " +
                "c.COLUMN_KEY, " +
                "c.EXTRA, " +
                "c.COLUMN_DEFAULT, " +
//                "t.TABLE_COMMENT " +
                "'' TABLE_COMMENT " +
                "FROM INFORMATION_SCHEMA.COLUMNS c " +
//                "LEFT JOIN INFORMATION_SCHEMA.TABLES t " +
//                "ON c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA " +
                "WHERE c.TABLE_SCHEMA = DATABASE() " +
                "ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION";

        List<Map<String, Object>> columnRows = jdbcTemplate.queryForList(columnSql);

        // 查询所有索引信息
        String indexSql = "SELECT " +
                "t.TABLE_NAME, " +
                "s.INDEX_NAME, " +
                "s.NON_UNIQUE, " +
                "s.COLUMN_NAME, " +
                "s.COLLATION as COLUMN_ORDER, " +
                "s.INDEX_TYPE, " +
                "s.INDEX_COMMENT " +
                "FROM INFORMATION_SCHEMA.TABLES t " +
                "LEFT JOIN INFORMATION_SCHEMA.STATISTICS s ON t.TABLE_NAME = s.TABLE_NAME AND t.TABLE_SCHEMA = s.TABLE_SCHEMA " +
                "WHERE t.TABLE_SCHEMA = DATABASE() AND s.INDEX_NAME IS NOT NULL " +
                "ORDER BY t.TABLE_NAME, s.INDEX_NAME, s.SEQ_IN_INDEX";

        List<Map<String, Object>> indexRows = jdbcTemplate.queryForList(indexSql);

        // 按表名分组处理列结果
        Map<String, List<Map<String, Object>>> tableColumnRows = new HashMap<>();
        for (Map<String, Object> row : columnRows) {
            String tableName = (String) row.get("TABLE_NAME");
            tableColumnRows.computeIfAbsent(tableName, k -> new ArrayList<>()).add(row);
        }

        // 按表名分组处理索引结果
        Map<String, List<Map<String, Object>>> tableIndexRows = new HashMap<>();
        for (Map<String, Object> row : indexRows) {
            String tableName = (String) row.get("TABLE_NAME");
            tableIndexRows.computeIfAbsent(tableName, k -> new ArrayList<>()).add(row);
        }

        // 构建每个表的TableInfo对象
        for (Map.Entry<String, List<Map<String, Object>>> entry : tableColumnRows.entrySet()) {
            String tableName = entry.getKey();
            List<Map<String, Object>> tableData = entry.getValue();

            TableInfo tableInfo = new TableInfo();
            tableInfo.setTableName(tableName);

            List<ColumnInfo> columns = new ArrayList<>();
            String tableComment = null;

            for (Map<String, Object> row : tableData) {
                ColumnInfo info = new ColumnInfo();
                info.setColumnName((String) row.get("COLUMN_NAME"));
                info.setDataType((String) row.get("FULL_DATA_TYPE")); // 使用完整数据类型
                info.setColumnComment((String) row.get("COLUMN_COMMENT"));
                info.setIsNullable((String) row.get("IS_NULLABLE"));
                info.setColumnKey((String) row.get("COLUMN_KEY"));
                info.setExtra((String) row.get("EXTRA"));
                info.setDefaultValue((String) row.get("COLUMN_DEFAULT"));

                columns.add(info);

                // 获取表注释（只需要获取一次）
                if (tableComment == null && row.get("TABLE_COMMENT") != null) {
                    tableComment = (String) row.get("TABLE_COMMENT");
                }
            }

            tableInfo.setColumns(columns);
            tableInfo.setTableComment(tableComment);

            // 处理索引信息
            List<IndexInfo> indexes = new ArrayList<>();
            List<Map<String, Object>> indexData = tableIndexRows.get(tableName);
            if (indexData != null) {
                // 按索引名分组
                Map<String, List<Map<String, Object>>> indexGroups = new HashMap<>();
                for (Map<String, Object> row : indexData) {
                    String indexName = (String) row.get("INDEX_NAME");
                    indexGroups.computeIfAbsent(indexName, k -> new ArrayList<>()).add(row);
                }

                // 构建每个索引对象
                for (Map.Entry<String, List<Map<String, Object>>> indexEntry : indexGroups.entrySet()) {
                    String indexName = indexEntry.getKey();
                    List<Map<String, Object>> indexColumns = indexEntry.getValue();

                    IndexInfo indexInfo = new IndexInfo();
                    indexInfo.setIndexName(indexName);

                    // 设置索引类型
                    String indexType = "INDEX";
                    if ("PRIMARY".equals(indexName)) {
                        indexType = "PRIMARY";
                    } else {
                        Map<String, Object> firstRow = indexColumns.get(0);
                        boolean nonUnique = ((Number) firstRow.get("NON_UNIQUE")).intValue() != 0;

                        if (!nonUnique) {
                            indexType = "UNIQUE";
                        }
                    }
                    indexInfo.setIndexType(indexType);

                    // 设置索引方法（BTREE, HASH等）
                    Map<String, Object> firstRow = indexColumns.get(0);
                    String indexMethod = (String) firstRow.get("INDEX_TYPE");
                    indexInfo.setIndexMethod(indexMethod);

                    // 收集列名和排序信息
                    List<String> columnNames = new ArrayList<>();
                    List<String> columnOrders = new ArrayList<>();

                    for (Map<String, Object> row : indexColumns) {
                        columnNames.add((String) row.get("COLUMN_NAME"));
                        String collation = (String) row.get("COLUMN_ORDER");
                        columnOrders.add("D".equals(collation) ? "DESC" : "ASC");
                    }

                    indexInfo.setColumnNames(columnNames);
                    indexInfo.setColumnOrders(columnOrders);
                    indexInfo.setNonUnique(!"PRIMARY".equals(indexName) && ((Number) firstRow.get("NON_UNIQUE")).intValue() != 0);
                    indexes.add(indexInfo);
                }
            }

            tableInfo.setIndexes(indexes);
            tableInfos.put(tableName, tableInfo);
        }

        return tableInfos;
    }

    // 新增方法：对比两个TableInfo对象
    private Map<String, Object> compareTwoTableInfos(TableInfo masterInfo, TableInfo targetInfo) {
        Map<String, Object> result = new HashMap<>();

        try {
            // 比较逻辑
            List<String> differences = new ArrayList<>();
            DatabaseCompareUtil.compareTableComments(masterInfo, targetInfo, differences);
            DatabaseCompareUtil.compareColumns(masterInfo, targetInfo, differences);
            DatabaseCompareUtil.compareIndexes(masterInfo, targetInfo, differences);

            result.put("table1", masterInfo); // 主库
            result.put("table2", targetInfo); // 目标库
            result.put("differences", differences);
            result.put("hasDifference", !differences.isEmpty());

            // 标记差异的列（只标记目标库与主库不一致的地方）
            DatabaseCompareUtil.markColumnDifferences(masterInfo, targetInfo);
            DatabaseCompareUtil.markIndexDifferences(masterInfo, targetInfo);

            // 生成SQL修改语句（将目标库修改为主库结构）
            if (!differences.isEmpty()) {
                List<String> alterSqlStatements = DatabaseCompareUtil.generateAlterSqlStatements(masterInfo, targetInfo, masterInfo.getTableName(), "");
                result.put("alterSqlStatements", alterSqlStatements);
            }

        } catch (Exception e) {
            result.put("hasDifference", true);
            result.put("error", "比较表 " + masterInfo.getTableName() + " 时发生错误: " + e.getMessage());
        }

        return result;
    }

    // 优化 getTableInfo 方法，使用更高效的查询
    private TableInfo getTableInfo(JdbcTemplate jdbcTemplate, String tableName) {
        // 一次性查询表的所有信息，包括列和表注释，包含完整的数据类型信息（包括长度）
        String columnSql = "SELECT " +
                "c.COLUMN_NAME, " +
                "CASE " +
                "  WHEN c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CONCAT(c.DATA_TYPE, '(', c.CHARACTER_MAXIMUM_LENGTH, ')') " +
                "  WHEN c.DATETIME_PRECISION IS NOT NULL AND c.DATA_TYPE = 'datetime' THEN CONCAT(c.DATA_TYPE, '(', c.DATETIME_PRECISION, ')') " +
                "  WHEN c.DATETIME_PRECISION IS NOT NULL AND c.DATA_TYPE = 'time' THEN CONCAT(c.DATA_TYPE, '(', c.DATETIME_PRECISION, ')') " +
                "  WHEN c.NUMERIC_PRECISION IS NOT NULL AND c.DATA_TYPE IN ('decimal', 'numeric') AND c.NUMERIC_SCALE IS NOT NULL THEN CONCAT(c.DATA_TYPE, '(', c.NUMERIC_PRECISION, ',', c.NUMERIC_SCALE, ')') " +
                "  WHEN c.NUMERIC_PRECISION IS NOT NULL AND c.DATA_TYPE IN ('decimal', 'numeric') THEN CONCAT(c.DATA_TYPE, '(', c.NUMERIC_PRECISION, ')') " +
                "  WHEN c.NUMERIC_PRECISION IS NOT NULL AND c.DATA_TYPE IN ('tinyint', 'smallint', 'mediumint', 'int', 'bigint') THEN CONCAT(c.DATA_TYPE, '(', c.NUMERIC_PRECISION, ')') " +
                "  ELSE c.DATA_TYPE " +
                "END AS FULL_DATA_TYPE, " +
                "c.COLUMN_COMMENT, " +
                "c.IS_NULLABLE, " +
                "c.COLUMN_KEY, " +
                "c.EXTRA, " +
                "c.COLUMN_DEFAULT, " +
//                "t.TABLE_COMMENT " +
                "'' TABLE_COMMENT " +
                "FROM INFORMATION_SCHEMA.COLUMNS c " +
//                "LEFT JOIN INFORMATION_SCHEMA.TABLES t " +
//                "ON c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA " +
                "WHERE c.TABLE_SCHEMA = DATABASE() AND c.TABLE_NAME = ? " +
                "ORDER BY c.ORDINAL_POSITION";

        List<Map<String, Object>> columnRows = jdbcTemplate.queryForList(columnSql, tableName);

        if (columnRows.isEmpty()) {
            return null;
        }

        TableInfo tableInfo = new TableInfo();
        tableInfo.setTableName(tableName);

        List<ColumnInfo> columns = new ArrayList<>();
        String tableComment = null;

        for (Map<String, Object> row : columnRows) {
            ColumnInfo info = new ColumnInfo();
            info.setColumnName((String) row.get("COLUMN_NAME"));
            info.setDataType((String) row.get("FULL_DATA_TYPE")); // 使用完整数据类型
            info.setColumnComment((String) row.get("COLUMN_COMMENT"));
            info.setIsNullable((String) row.get("IS_NULLABLE"));
            info.setColumnKey((String) row.get("COLUMN_KEY"));
            info.setExtra((String) row.get("EXTRA"));
            info.setDefaultValue((String) row.get("COLUMN_DEFAULT"));

            columns.add(info);

            // 获取表注释（只需要获取一次）
            if (tableComment == null && row.get("TABLE_COMMENT") != null) {
                tableComment = (String) row.get("TABLE_COMMENT");
            }
        }

        tableInfo.setColumns(columns);
        tableInfo.setTableComment(tableComment);

        // 查询索引信息
        String indexSql = "SELECT " +
                "s.INDEX_NAME, " +
                "s.NON_UNIQUE, " +
                "s.COLUMN_NAME, " +
                "s.COLLATION as COLUMN_ORDER, " +
                "s.INDEX_TYPE, " +
                "s.INDEX_COMMENT " +
                "FROM INFORMATION_SCHEMA.STATISTICS s " +
                "WHERE s.TABLE_SCHEMA = DATABASE() AND s.TABLE_NAME = ? " +
                "ORDER BY s.INDEX_NAME, s.SEQ_IN_INDEX";

        List<Map<String, Object>> indexRows = jdbcTemplate.queryForList(indexSql, tableName);
        List<IndexInfo> indexes = new ArrayList<>();

        if (!indexRows.isEmpty()) {
            // 按索引名分组
            Map<String, List<Map<String, Object>>> indexGroups = new HashMap<>();
            for (Map<String, Object> row : indexRows) {
                String indexName = (String) row.get("INDEX_NAME");
                indexGroups.computeIfAbsent(indexName, k -> new ArrayList<>()).add(row);
            }

            // 构建每个索引对象
            for (Map.Entry<String, List<Map<String, Object>>> indexEntry : indexGroups.entrySet()) {
                String indexName = indexEntry.getKey();
                List<Map<String, Object>> indexColumns = indexEntry.getValue();

                IndexInfo indexInfo = new IndexInfo();
                indexInfo.setIndexName(indexName);

                // 设置索引类型
                String indexType = "INDEX";
                if ("PRIMARY".equals(indexName)) {
                    indexType = "PRIMARY";
                } else {
                    Map<String, Object> firstRow = indexColumns.get(0);
                    boolean nonUnique = ((Number) firstRow.get("NON_UNIQUE")).intValue() != 0;

                    if (!nonUnique) {
                        indexType = "UNIQUE";
                    }
                }
                indexInfo.setIndexType(indexType);

                // 设置索引方法（BTREE, HASH等）
                Map<String, Object> firstRow = indexColumns.get(0);
                String indexMethod = (String) firstRow.get("INDEX_TYPE");
                indexInfo.setIndexMethod(indexMethod);

                // 收集列名和排序信息
                List<String> columnNames = new ArrayList<>();
                List<String> columnOrders = new ArrayList<>();

                for (Map<String, Object> row : indexColumns) {
                    columnNames.add((String) row.get("COLUMN_NAME"));
                    String collation = (String) row.get("COLUMN_ORDER");
                    columnOrders.add("D".equals(collation) ? "DESC" : "ASC");
                }

                indexInfo.setColumnNames(columnNames);
                indexInfo.setColumnOrders(columnOrders);
                indexInfo.setNonUnique(!"PRIMARY".equals(indexName) && ((Number) firstRow.get("NON_UNIQUE")).intValue() != 0);
                indexes.add(indexInfo);
            }
        }

        tableInfo.setIndexes(indexes);

        return tableInfo;
    }
}
